package bc.running.forum.db;

import java.io.BufferedWriter;
import java.io.Console;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import bc.running.forum.db.meta.Column;
import bc.running.forum.db.meta.DBSchema;
import bc.running.forum.db.meta.Table;
import bc.running.forum.db.translators.ClobDataTranslator;
import bc.running.forum.db.translators.IDataTranslator;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import net.sourceforge.jtds.jdbcx.JtdsDataSource;

public class Main {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		System.out.println("Working...");
		String password = getPassword();
		DataSource dataSource = getDataSource(config, password);
		System.out.println("Connecting to DB with the following details: "
				+ config.toString());
		System.out.println();
		DBSchema dbSchema = null;
		SchemaExtractor extractor = new SchemaExtractor(dataSource);
		try {
			String[] types = config.getType() == DBType.MySQL ? null
					: new String[] { "TABLE" };
			dbSchema = extractor.extract(null, config.getDbName(), null, types);
			List<Table> tables = dbSchema.getTables();
			StringBuilder createTableBuilder = new StringBuilder();
			StringBuilder dropTableBuilder = new StringBuilder();
			StringBuilder truncateTableBuilder = new StringBuilder();

			for (Table table : tables) {
				getCreateTablePhrase(table, createTableBuilder);
				getDropTablePhrase(table, dropTableBuilder);
				getTruncateTablePhrase(table, truncateTableBuilder);
				createTableBuilder.append(NL);
				dropTableBuilder.append(NL);
				truncateTableBuilder.append(NL);
				queryTableAndDumpData(dataSource, table);
			}
			dumpCreateTablesScript(createTableBuilder);
			dumpDropTablesScript(dropTableBuilder);
			dumpTruncateTablesScript(truncateTableBuilder);
		} catch (Exception e) {
			e.printStackTrace();
			System.exit(1);
		}
		if(config.isZipoutfile()) {
			try {
				Utils.zipOutputFolder(config.getDbName());
			} catch (Exception e) {
				System.err.println("Failed to zip output folder.");
				e.printStackTrace();
			}
		}
		printFinalMsg(dbSchema);

	}
	private static void printFinalMsg(DBSchema dbSchema) {
		List<Table> tables = dbSchema.getTables();
		if (tables != null) {
			final int size = tables.size();
			final String wasOrWere = size > 1 ? "were" : "was";
			System.out.println("The end: " + NL + size + " table[s] "
					+ wasOrWere
					+ " processed. See the 'out' folder for results.");
		} else {
			System.out.println("The end - no tables found.");
		}
	}
	/**
	 * Read the password in two ways:
	 *  - via console
	 *  - via system property
	 *  If the console is available - use the console, else - use a system property
	 * @return password
	 */
	private static String getPassword() {
		Console console = System.console();
		String password;
		if (console == null) {
			password = System.getProperty("db.password") != null ? System.getProperty("db.password") : ""; 
		} else {
			char[] passwordChars = console.readPassword("Enter DB password: ");
			password = new String(passwordChars);
		}
		return password;
	}

	private static void getDropTablePhrase(Table table,
			StringBuilder dropTableBuilder) {
		dropTableBuilder.append("DROP TABLE ").append(table.getName())
				.append(';');
	}

	private static void getTruncateTablePhrase(Table table,
			StringBuilder truncateTableBuilder) {
		truncateTableBuilder.append("TRUNCATE TABLE ").append(table.getName())
				.append(';');
	}

	private static void dumpCreateTablesScript(StringBuilder builder)
			throws Exception {
		dumpScript(builder, "create_tables.sql");
	}

	private static void dumpDropTablesScript(StringBuilder builder)
			throws Exception {
		dumpScript(builder, "drop_tables.sql");
	}

	private static void dumpTruncateTablesScript(StringBuilder builder)
			throws Exception {
		dumpScript(builder, "truncate_tables.sql");

	}

	private static void dumpScript(StringBuilder builder, String fileName)
			throws Exception {
		FileWriter outFile = new FileWriter("out/" + fileName);
		PrintWriter out = new PrintWriter(outFile);
		out.println("/* Script was auto generated at " + new Date() + " */");
		out.print(builder.toString());
		out.close();
	}

	/**
	 * Get a table definition and generate MySQL 'CREATE TABLE' SQL
	 * 
	 * @param table
	 * @param builder
	 */
	private static void getCreateTablePhrase(Table table, StringBuilder builder) {
		List<Column> columns = table.getColumns();
		builder.append("CREATE TABLE ").append(table.getName()).append(' ')
				.append('(').append(NL);
		for (Column column : columns) {
			String dataType = mapDataType(column.getColumnDataType());
			boolean matchWasFound = true;
			if (dataType == null) {
				dataType = column.getColumnDataType();
				matchWasFound = false;
			}
			String columnName = handleReservedWord(column.getColumnName());
			builder.append('\t').append(columnName).append(' ')
					.append(dataType);
			if (!matchWasFound) {
				if (column.getColumnSize() != null) {
					builder.append('(').append(column.getColumnSize())
							.append(')');
				}
			}
			if (column.getColumnNullable().equals("NO")) {
				builder.append(" NOT NULL");
			}
			builder.append(",").append(NL);

		}
		String pkPhrase = getPKPhrase(columns);
		if (!pkPhrase.isEmpty()) {
			builder.append('\t').append(pkPhrase);
		}
		builder.append(NL).append(");").append(NL);

	}

	/**
	 * Generate the MySQL Primary Key phrase
	 * 
	 * @param columns
	 * @return
	 */
	private static String getPKPhrase(List<Column> columns) {
		String identity = getIdentity(columns);
		if (identity != null) {
			return "PRIMARY KEY(" + identity + ")";
		}
		StringBuilder builder = new StringBuilder();
		for (Column column : columns) {
			if (column.isPk()) {
				if (builder.length() == 0) {
					builder.append("PRIMARY KEY(");
				}
				builder.append(column.getColumnName()).append(',');
			}
		}
		if (builder.length() != 0) {
			builder.setLength(builder.length() - 1);
			builder.append(')');
		}
		return builder.toString();

	}

	private static String getIdentity(List<Column> columns) {
		for (Column column : columns) {
			if (column.getColumnDataType().equalsIgnoreCase("identity")) {
				return column.getColumnName();
			}
		}
		return null;

	}

	/**
	 * Dump the table data to XML file that can be imported to MySQL
	 * 
	 * @param dataSource
	 * @param table
	 * @throws Exception
	 */
	private static void queryTableAndDumpData(DataSource dataSource, Table table)
			throws Exception {

		final String selectPhrase = getSelectPhrase(table.getName());
		Connection conn = dataSource.getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(selectPhrase);			
		} catch(SQLException e) {
			System.err.println("Failed to query table [" + table.getName() + "]. The error is [" + e.getMessage() + "].");
			return;
		}
		System.out.print("Processing table: " + table.getName() + "... ");
		ITableHandler tableHandler = tableHandlers.get(table.getName());
		if(tableHandler != null) {
			System.out.println( " will be handled by " + tableHandler.getClass().getName());
			List<String> listOfTables = tableHandler.handleTableData(rs);
			if(listOfTables != null && !listOfTables.isEmpty()) {
				System.out.println("The following files were generated: " + listOfTables);
			}
			return;
		}
		BufferedWriter out = new BufferedWriter(new OutputStreamWriter
				  (new FileOutputStream(new File("out/" + table.getName() + ".xml")),"UTF8"));
		Utils.writeln(out,"<" + table.getName() + ">");
		int counter = 0;
		while (rs.next()) {
			List<Column> columns = table.getColumns();
			Utils.writeln(out,"<row>");
			for (Column column : columns) {
				Object object = rs.getObject(column.getColumnName());
				object = translate(column.getColumnDataType(), object);
				if (object instanceof String) {
					String data = Utils.xmlEscaping((String) object);
					object = data;
				}
				String field = "<field name=\"" + column.getColumnName() + "\">"
						+ object + "</field>";
				Utils.writeln(out,field);
			}
			counter++;
			Utils.writeln(out,"</row>");
		}
		System.out.println("[" + counter + " record[s]].");
		Utils.writeln(out,"</" + table.getName() + ">");
		out.close();
	}
	
	private static String getSelectPhrase(final String tableName) {
		String select = selects.get(tableName);
		if(select != null) {
			return select;
		} else {
			return "SELECT * FROM " + tableName;
		}
	}
	

	/**
	 * Map SQLServer datatype to MySQL datatype
	 * 
	 * @param sqlServerDataType
	 * @return
	 */
	private static String mapDataType(String sqlServerDataType) {
		if (sqlServerDataType == null) {
			return null;
		}
		String value = dataTypesMapping.get(sqlServerDataType);
		if (value != null) {
			return value;
		} else {
			return dataTypesMapping.get(sqlServerDataType.toUpperCase());
		}

	}

	private static DataSource getDataSource(Config config, String password) {
		switch (config.getType()) {
		case SQLServer:
			return getSQLServerDataSource(config, password);
		case MySQL:
			return getMySQLDataSource(config, password);
		default:
			return null;
		}
	}

	private static DataSource getSQLServerDataSource(Config config,
			String password) {
		JtdsDataSource dataSource = new JtdsDataSource();
		dataSource.setUser(config.getUser());
		dataSource.setPassword(password);
		dataSource.setServerName(config.getHost());
		dataSource.setPortNumber(config.getPort());
		dataSource.setDatabaseName(config.getDbName());
		return dataSource;
	}

	private static DataSource getMySQLDataSource(Config config, String password) {
		MysqlDataSource dataSource = new MysqlDataSource();
		dataSource.setUser(config.getUser());
		dataSource.setPassword(password);
		dataSource.setServerName(config.getHost());
		dataSource.setPortNumber(config.getPort());
		dataSource.setDatabaseName(config.getDbName());
		return dataSource;

	}

	private static String handleReservedWord(String word) {
		if (mySqlReservedWords.contains(word)) {
			return "_" + word;
		} else {
			return word;
		}

	}
	
	private static Object translate(String columnType,Object src) throws Exception{
		IDataTranslator translator = translators.get(columnType.toUpperCase());
		if(translator == null) {
			return src;
		} else {
			return translator.translate(src);
		}
	}

	/**
	 * Mapping between SQLServer field types and MySQL field types Based on the
	 * document "Guide to Migrating from Microsoft SQL Server to MySQL"
	 */
	private static Map<String, String> dataTypesMapping = new HashMap<String, String>();
	static {
		dataTypesMapping.put("IDENTITY", "AUTO_INCREMENT");
		dataTypesMapping.put("NTEXT", "TEXT CHARACTER SET UTF8");
		dataTypesMapping.put("NATIONAL TEXT", "TEXT CHARACTER SET UTF8");
		dataTypesMapping.put("SMALLDATETIME", "DATETIME");
		dataTypesMapping.put("MONEY", "DECIMAL(19,4)");
		dataTypesMapping.put("SMALL MONEY", "DECIMAL(10,4)");
		dataTypesMapping.put("UNIQUEIDENTIFIER", "BINARY(16)");
		dataTypesMapping.put("SYSNAME", "CHAR(256)");

	}

	private static Config config = new Config();

	private static Set<String> mySqlReservedWords = new HashSet<String>();

	static {
		List<String> list = Arrays.asList(config.getMysqlReservedWords().split(
				","));
		mySqlReservedWords.addAll(list);

	}
	private static final String NL = System.getProperty("line.separator");
	
	private static Map<String,IDataTranslator> translators = new HashMap<String,IDataTranslator>();
	static {
		translators.put("NTEXT", new ClobDataTranslator());
		translators.put("NATIONAL TEXT", new ClobDataTranslator());		
	}
	
	private static Map<String,ITableHandler> tableHandlers = new HashMap<String,ITableHandler>();
	static {
		tableHandlers.put("PORTAL_REPLY", new PostsTableHandler());
		tableHandlers.put("PORTAL_TOPICS", new TopicsTableHandler());
	}
	
	private static Map<String,String> selects = new HashMap<String,String>();
	static {
		selects.put("PORTAL_REPLY", "SELECT * FROM PORTAL_REPLY ORDER BY REPLY_ID ASC");
		selects.put("PORTAL_TOPICS","SELECT * FROM PORTAL_TOPICS ORDER BY TOPIC_ID ASC");
	}

}
